package com.drg.center.dao;

import cn.hutool.core.util.ArrayUtil;
import com.alibaba.fastjson.JSONObject;
import com.drg.center.entity.PatientCaseEsPO;
import com.drg.center.entity.param.DoctorGroupSummaryDTO;
import com.drg.center.entity.param.DoctorSummaryDTO;
import com.drg.center.entity.result.*;
import com.drg.center.utils.BusinessDateUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.guaike.core.entity.exception.ApiException;
import com.guaike.core.utils.BigDecimalUtil;
import com.guaike.core.utils.CompareUtil;
import com.guaike.core.utils.LocalDateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.lucene.search.join.ScoreMode;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.script.Script;
import org.elasticsearch.script.ScriptType;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.aggregations.*;
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramAggregationBuilder;
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramInterval;
import org.elasticsearch.search.aggregations.bucket.histogram.Histogram;
import org.elasticsearch.search.aggregations.bucket.range.Range;
import org.elasticsearch.search.aggregations.bucket.range.RangeAggregationBuilder;
import org.elasticsearch.search.aggregations.bucket.terms.ParsedStringTerms;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.elasticsearch.core.ElasticsearchOperations;
import org.springframework.data.elasticsearch.core.query.NativeSearchQuery;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import static org.elasticsearch.index.query.QueryBuilders.boolQuery;

/**
 * @Author: zhangxilin
 * @Date: 2025/1/5
 */
@Slf4j
@Repository
public class EsDoctorGroupAngleDAO {

    @Autowired
    private ElasticsearchOperations elasticsearchOperations;

    /**
     * 医生组盈亏汇总(DSL-100)
     * @param param
     * @return
     */
    public List<DoctorGoupSummaryVO> getDoctorGoupSummaryVO(DoctorGroupSummaryDTO param){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));

        if(StringUtils.isNotBlank(param.getDoctorCode())){
            queryBuilder.must(QueryBuilders.termQuery("doctorCode", param.getDoctorCode()));
        }
        if(StringUtils.isNotBlank(param.getDoctorGroupCode())){
            queryBuilder.must(QueryBuilders.termQuery("doctorGroupCode", param.getDoctorGroupCode()));
        }

        if(param.getStartDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("inHospitalDate").gte(LocalDateUtil.getTimestamp(param.getStartDate())));
        }
        if(param.getEndDate() != null){
            queryBuilder.must(QueryBuilders.rangeQuery("inHospitalDate").lt(LocalDateUtil.getTimestamp(param.getEndDate().plusDays(1))));
        }
        if(StringUtils.isNotBlank(param.getSearch())){
            BoolQueryBuilder shouldQuery = QueryBuilders.boolQuery();
            shouldQuery.should(QueryBuilders.termQuery("caseCode", param.getSearch()));
            shouldQuery.should(QueryBuilders.matchPhraseQuery("doctorName", param.getSearch()));
            shouldQuery.should(QueryBuilders.nestedQuery("personInfoVO",
                    QueryBuilders.matchPhraseQuery("personInfoVO.patientName", param.getSearch()),
                    ScoreMode.None));
            shouldQuery.minimumShouldMatch("1");
            queryBuilder.must(shouldQuery);
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("doctorGroupAgg")
                        .field("doctorGroupCode")
                        .size(20000)
                        .order(BucketOrder.aggregation("total_profit", false))
                        .subAggregation(AggregationBuilders.sum("total_profit").field("profit").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("expectProfit").field("expectProfit").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("medicineCost").field("medicineCost").format("0.00"))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.avg("avg_profit").field("profit").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_expectProfit").field("expectProfit").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_medicineCost").field("medicineCost").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_consumablesCost").field("consumablesCost").format("0.00"))
                        .subAggregation(AggregationBuilders.topHits("doctorGroupName").size(1)
                                .fetchSource(new String[]{"doctorGroupName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations = elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorGroupAgg = (ParsedStringTerms) aggregations.get("doctorGroupAgg");

        List<DoctorGoupSummaryVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : doctorGroupAgg.getBuckets()) {
            DoctorGoupSummaryVO vo = new DoctorGoupSummaryVO();

            vo.setDoctorGroupCode(bucket.getKeyAsString());
            //名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("doctorGroupName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDoctorGroupName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("doctorGroupName"));
            }

            // 净盈亏金额
            ParsedSum totalProfitSum = bucket.getAggregations().get("total_profit");
            vo.setProfit(BigDecimalUtil.parseBigDecimal(totalProfitSum.getValueAsString()));

            //预测盈亏金额
            ParsedSum expectProfitSum = bucket.getAggregations().get("expectProfit");
            vo.setProfit(BigDecimalUtil.parseBigDecimal(expectProfitSum.getValueAsString()));

            //医疗总费用
            ParsedSum totalCostSum = bucket.getAggregations().get("totalCost");
            vo.setTotalCost(BigDecimalUtil.parseBigDecimal(totalCostSum.getValueAsString()));

            //药品费用
            ParsedSum medicineCostSum = bucket.getAggregations().get("medicineCost");
            BigDecimal medicineCost = BigDecimalUtil.parseBigDecimal(medicineCostSum.getValueAsString());
            if(vo.getTotalCost() != null &&  medicineCost != null  && !CompareUtil.isEqual(vo.getTotalCost(), BigDecimal.ZERO)){
                vo.setDrugMix(BigDecimalUtil.formatBigDecimal(medicineCost.divide(vo.getTotalCost(), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100))) + "%");
            }
            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount((int)caseCount.getValue());

            //例均净盈亏金额
            ParsedAvg profitAvg = bucket.getAggregations().get("avg_profit");
            vo.setAvgProfit(BigDecimalUtil.parseBigDecimal(profitAvg.getValueAsString()));

            //例均预测盈亏
            ParsedAvg expectProfitAvg = bucket.getAggregations().get("avg_expectProfit");
            vo.setAvgExpectProfit(BigDecimalUtil.parseBigDecimal(expectProfitAvg.getValueAsString()));

            //例均费用
            ParsedAvg totalCostAvg = bucket.getAggregations().get("avg_totalCost");
            vo.setAvgTotalCost(BigDecimalUtil.parseBigDecimal(totalCostAvg.getValueAsString()));

            //例均药品费用
            ParsedAvg medicineCostAvg = bucket.getAggregations().get("avg_medicineCost");
            vo.setAvgMedicineCost(BigDecimalUtil.parseBigDecimal(medicineCostAvg.getValueAsString()));

            //例均材料费用
            ParsedAvg consumablesCostAvg = bucket.getAggregations().get("avg_consumablesCost");
            vo.setAvgConsumablesCost(BigDecimalUtil.parseBigDecimal(consumablesCostAvg.getValueAsString()));

            list.add(vo);
        }
        log.info("【EsDoctorGroupAngleDAO.getDoctorGoupSummaryVO】 list: {}", JSONObject.toJSONString(list));
        return list;

    }

    /**
     * 医生组盈亏情况(DSL-101)
     * @param startDate
     * @param endDate
     * @return
     */
    public List<KeyValueVO> getDoctorGroupProfitStt(LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        // 构建聚合
        TermsAggregationBuilder departmentsAgg = AggregationBuilders.terms("doctorGroupAgg")
                .field("doctorGroupCode")
                .order(BucketOrder.aggregation("total_profit", false));

        SumAggregationBuilder totalProfitAgg = AggregationBuilders.sum("total_profit").field("profit").format("0.00");

        TopHitsAggregationBuilder departNameAgg = AggregationBuilders.topHits("doctorGroupName").size(1)
                .fetchSource(new String[]{"doctorGroupName"}, null);


        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .withPageable(PageRequest.of(0, 1))
                .addAggregation(departmentsAgg.subAggregation(totalProfitAgg).subAggregation(departNameAgg))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorGroupAgg = aggregations.get("doctorGroupAgg");

        List<KeyValueVO> list = Lists.newArrayList();
        for (Terms.Bucket entry : doctorGroupAgg.getBuckets()) {
            String departmentCode = entry.getKeyAsString();
            ParsedSum sum = entry.getAggregations().get("total_profit");
            BigDecimal totalProfit = BigDecimalUtil.parseBigDecimal(sum.getValueAsString());

            TopHits topHits = entry.getAggregations().get("doctorGroupName");
            SearchHit[] hits = topHits.getHits().getHits();

            String departmentName = null;
            if (!ArrayUtil.isEmpty(hits)) {
                Map<String, Object> sourceAsMap = hits[0].getSourceAsMap();
                departmentName = (String) sourceAsMap.get("doctorGroupName");
            }
            list.add(new KeyValueVO(departmentCode,totalProfit, departmentName) );
        }
        log.info("【EsDoctorGroupAngleDAO.getDoctorGroupProfitStt】 list: {}", JSONObject.toJSONString(list));
        return list;
    }


    /**
     * 医生组盈亏占比(DSL-102)
     * @param startDate
     * @param endDate
     * @return
     */
    public ProfitLossMixVO getDoctorGroupProfitRateStt(LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        // 构建聚合
        TermsAggregationBuilder departmentsAgg = AggregationBuilders.terms("profit_aggs")
                .field("doctorGroupCode")
                .size(1000);

        SumAggregationBuilder totalProfitAgg = AggregationBuilders.sum("total_profit")
                .field("profit").format("0.00");

        departmentsAgg.subAggregation(totalProfitAgg);

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(departmentsAgg)
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();

        // 解析部门聚合
        Terms terms = aggregations.get("profit_aggs");
        int total = 0;
        int profitCount = 0;
        int lossCount = 0;
        for (Terms.Bucket entry: terms.getBuckets()){
            // 解析利润总和
            ParsedSum sumProfit = entry.getAggregations().get("total_profit");
            double totalProfit = sumProfit.getValue();
            ++total;
            if(totalProfit >=0 ){
                ++ profitCount;
            }else{
                ++lossCount;
            }
        }

        BigDecimal rate = BigDecimalUtil.calculatePercentage(profitCount, total);

        ProfitLossMixVO vo = new ProfitLossMixVO();
        vo.setAllCount(total);
        vo.setProfitCount(profitCount);
        vo.setLossCount(lossCount);
        vo.setProfitRatio(rate+"");

        log.info("【EsDoctorGroupAngleDAO.getDoctorGroupProfitRateStt】 vo: {}",JSONObject.toJSONString(vo));
        return vo;
    }

    /**
     *  医生组盈亏总趋势(DSL-103)
     * @param year
     * @return
     */
    public ChartLineVO getDoctorGroupProfitTrendVO(Integer year){

        if(year == null){
            throw new ApiException("请选择年份");
        }

        List<String> dataX = BusinessDateUtil.getMonthList(year);


        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));


        LocalDateTime firstDayOfYear = LocalDateUtil.getFirstDayOfYear(year);
        LocalDateTime lastDayOfYear = LocalDateUtil.getLastDayOfYear(year);
        queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(firstDayOfYear)));
        queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lte(LocalDateUtil.getTimestamp(lastDayOfYear)));

        DateHistogramAggregationBuilder dateHistogramAgg = AggregationBuilders.dateHistogram("monthly_profit")
                .field("outHospitalDate")
                .calendarInterval(DateHistogramInterval.MONTH)
                .format("yyyy-MM")
                .timeZone(ZoneId.of("+08:00"))
                .subAggregation(AggregationBuilders.sum("total_profit").field("profit").format("0.00"));

        TermsAggregationBuilder groupAgg = AggregationBuilders.terms("group_aggs")
                .field("doctorGroupCode")
                .size(10000)
                .subAggregation(AggregationBuilders.sum("total_profit")
                        .field("profit").format("0.00"))
                .subAggregation(AggregationBuilders.topHits("doctorGroupName").size(1)
                        .fetchSource(new String[]{"doctorGroupName"}, null))
                .subAggregation(dateHistogramAgg);



        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(groupAgg)
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();

        Terms groupTerms = aggregations.get("group_aggs");

        List<ChartLineSeriesVO> series = new ArrayList<>();

        for (Terms.Bucket entry : groupTerms.getBuckets()) {
            String doctorGroupCode = entry.getKeyAsString();

            ParsedSum sum = entry.getAggregations().get("total_profit");


            TopHits topHits = entry.getAggregations().get("doctorGroupName");
            SearchHit[] hits = topHits.getHits().getHits();
            String groupName = null;
            if (!ArrayUtil.isEmpty(hits)) {
                Map<String, Object> sourceAsMap = hits[0].getSourceAsMap();
                groupName = (String) sourceAsMap.get("doctorGroupName");
            }

            Histogram perMonthAgg = entry.getAggregations().get("monthly_profit");
            Map<String, Object> dataMap = getMonthlyMap(perMonthAgg);
            List<Object> dataYList = Lists.newArrayList();
            for(String month: dataX){
                Object value = dataMap.get(month);
                dataYList.add(ObjectUtils.isEmpty(value) ? null : value);
            }

            ChartLineSeriesVO seriesVO = new ChartLineSeriesVO();
            seriesVO.setSeriesName(groupName);
            seriesVO.setCode(doctorGroupCode);
            seriesVO.setDataList(dataYList);
            seriesVO.setIsProfit(sum.getValue() >=0 ? true: false);
            series.add(seriesVO);
        }

        ChartLineVO vo = new ChartLineVO();
        vo.setDataX(dataX);
        vo.setSeries(series);

        log.info("【EsDoctorGroupAngleDAO.getDoctorGroupProfitTrendVO】 vo: {}",JSONObject.toJSONString(vo));
        return vo;
    }


    /**
     * 医生盈亏汇总(DSL-104)
     * @param param
     * @return
     */

    public List<DoctorSummaryVO> getDoctorSummaryVO(DoctorSummaryDTO param) {
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));

        if (StringUtils.isNotBlank(param.getDoctorCode())) {
            queryBuilder.must(QueryBuilders.termQuery("doctorCode", param.getDoctorCode()));
        }

        if (param.getStartDate() != null) {
            queryBuilder.must(QueryBuilders.rangeQuery("inHospitalDate").gte(LocalDateUtil.getTimestamp(param.getStartDate())));
        }
        if (param.getEndDate() != null) {
            queryBuilder.must(QueryBuilders.rangeQuery("inHospitalDate").lt(LocalDateUtil.getTimestamp(param.getEndDate().plusDays(1))));
        }
        if (StringUtils.isNotBlank(param.getSearch())) {
            BoolQueryBuilder shouldQuery = QueryBuilders.boolQuery();
            shouldQuery.should(QueryBuilders.termQuery("caseCode", param.getSearch()));
            shouldQuery.should(QueryBuilders.matchPhraseQuery("doctorName", param.getSearch()));
            shouldQuery.should(QueryBuilders.nestedQuery("personInfoVO",
                    QueryBuilders.matchPhraseQuery("personInfoVO.patientName", param.getSearch()),
                    ScoreMode.None));
            shouldQuery.minimumShouldMatch("1");
            queryBuilder.must(shouldQuery);
        }

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(AggregationBuilders.terms("doctorAgg")
                        .field("doctorCode")
                        .size(20000)
                        .order(BucketOrder.aggregation("total_profit", false))
                        .subAggregation(AggregationBuilders.sum("total_profit").field("profit").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("expectProfit").field("expectProfit").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.sum("medicineCost").field("medicineCost").format("0.00"))
                        .subAggregation(AggregationBuilders.count("caseCount").field("caseCode"))
                        .subAggregation(AggregationBuilders.avg("avg_profit").field("profit").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_expectProfit").field("expectProfit").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_totalCost").field("totalCost").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_medicineCost").field("medicineCost").format("0.00"))
                        .subAggregation(AggregationBuilders.avg("avg_consumablesCost").field("consumablesCost").format("0.00"))
                        .subAggregation(AggregationBuilders.topHits("groupName").size(2)
                                .fetchSource(new String[]{"doctorName", "departName"}, null))
                )
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations = elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorGroupAgg = (ParsedStringTerms) aggregations.get("doctorAgg");

        List<DoctorSummaryVO> list = Lists.newArrayList();
        for (Terms.Bucket bucket : doctorGroupAgg.getBuckets()) {
            DoctorSummaryVO vo = new DoctorSummaryVO();

            vo.setDoctorCode(bucket.getKeyAsString());
            //名称
            TopHits departNameHits = (TopHits) bucket.getAggregations().get("groupName");
            if (!ArrayUtil.isEmpty(departNameHits.getHits().getHits())) {
                vo.setDoctorName((String) departNameHits.getHits().getHits()[0].getSourceAsMap().get("doctorName"));
                vo.setDepartName((String) departNameHits.getHits().getHits()[1].getSourceAsMap().get("departName"));
            }


            // 净盈亏金额
            ParsedSum totalProfitSum = bucket.getAggregations().get("total_profit");
            vo.setProfit(BigDecimalUtil.parseBigDecimal(totalProfitSum.getValueAsString()));

            //预测盈亏金额
            ParsedSum expectProfitSum = bucket.getAggregations().get("expectProfit");
            vo.setProfit(BigDecimalUtil.parseBigDecimal(expectProfitSum.getValueAsString()));

            //医疗总费用
            ParsedSum totalCostSum = bucket.getAggregations().get("totalCost");
            vo.setTotalCost(BigDecimalUtil.parseBigDecimal(totalCostSum.getValueAsString()));

            //药品费用
            ParsedSum medicineCostSum = bucket.getAggregations().get("medicineCost");
            BigDecimal medicineCost = BigDecimalUtil.parseBigDecimal(medicineCostSum.getValueAsString());
            if (vo.getTotalCost() != null && medicineCost != null && !CompareUtil.isEqual(vo.getTotalCost(), BigDecimal.ZERO)) {
                vo.setDrugMix(BigDecimalUtil.formatBigDecimal(medicineCost.divide(vo.getTotalCost(), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100))) + "%");
            }
            //病例数
            ValueCount caseCount = bucket.getAggregations().get("caseCount");
            vo.setCaseCount((int) caseCount.getValue());

            //例均净盈亏金额
            ParsedAvg profitAvg = bucket.getAggregations().get("avg_profit");
            vo.setAvgProfit(BigDecimalUtil.parseBigDecimal(profitAvg.getValueAsString()));

            //例均预测盈亏
            ParsedAvg expectProfitAvg = bucket.getAggregations().get("avg_expectProfit");
            vo.setAvgExpectProfit(BigDecimalUtil.parseBigDecimal(expectProfitAvg.getValueAsString()));

            //例均费用
            ParsedAvg totalCostAvg = bucket.getAggregations().get("avg_totalCost");
            vo.setAvgTotalCost(BigDecimalUtil.parseBigDecimal(totalCostAvg.getValueAsString()));

            //例均药品费用
            ParsedAvg medicineCostAvg = bucket.getAggregations().get("avg_medicineCost");
            vo.setAvgMedicineCost(BigDecimalUtil.parseBigDecimal(medicineCostAvg.getValueAsString()));

            //例均材料费用
            ParsedAvg consumablesCostAvg = bucket.getAggregations().get("avg_consumablesCost");
            vo.setAvgConsumablesCost(BigDecimalUtil.parseBigDecimal(consumablesCostAvg.getValueAsString()));

            list.add(vo);
        }
        log.info("【EsDoctorGroupAngleDAO.getDoctorGoupSummaryVO】size:{},  list: {}", list.size(), JSONObject.toJSONString(list.size() > 2 ? list.subList(0, 2) : list));
        return list;
    }


    /**
     *医生盈亏占比(DSL-105)
     * @param startDate
     * @param endDate
     * @return
     */
    public ProfitLossMixVO getDoctorProfitRateStt(LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        // 构建聚合
        TermsAggregationBuilder departmentsAgg = AggregationBuilders.terms("profit_aggs")
                .field("doctorCode")
                .size(1000);

        SumAggregationBuilder totalProfitAgg = AggregationBuilders.sum("total_profit")
                .field("profit").format("0.00");

        departmentsAgg.subAggregation(totalProfitAgg);

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(departmentsAgg)
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();

        // 解析部门聚合
        Terms terms = aggregations.get("profit_aggs");
        int total = 0;
        int profitCount = 0;
        int lossCount = 0;
        for (Terms.Bucket entry: terms.getBuckets()){
            // 解析利润总和
            ParsedSum sumProfit = entry.getAggregations().get("total_profit");
            double totalProfit = sumProfit.getValue();
            ++total;
            if(totalProfit >=0 ){
                ++ profitCount;
            }else{
                ++lossCount;
            }
        }

        BigDecimal rate = BigDecimalUtil.calculatePercentage(profitCount, total);

        ProfitLossMixVO vo = new ProfitLossMixVO();
        vo.setAllCount(total);
        vo.setProfitCount(profitCount);
        vo.setLossCount(lossCount);
        vo.setProfitRatio(rate+"");

        log.info("【EsDoctorGroupAngleDAO.getDoctorProfitRateStt】 vo: {}",JSONObject.toJSONString(vo));
        return vo;
    }


    /**
     *  医生盈利排名TOP5(DSL-106)
     * @param startDate
     * @param endDate
     * @return
     */
    public List<KeyValueVO> getDoctorProfitTop5(LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        // 构建聚合
        TermsAggregationBuilder departmentsAgg = AggregationBuilders.terms("departments")
                .field("doctorCode")
                .size(5)
                .order(BucketOrder.aggregation("total_profit", false));

        SumAggregationBuilder totalProfitAgg = AggregationBuilders.sum("total_profit").field("profit").format("0.00");

        TopHitsAggregationBuilder departNameAgg = AggregationBuilders.topHits("doctorName").size(1)
                .fetchSource(new String[]{"doctorName"}, null);

        Map profitMap = Maps.newHashMap();
        profitMap.put("profitSum", "total_profit");

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .withPageable(PageRequest.of(0, 1))
                .addAggregation(departmentsAgg.subAggregation(totalProfitAgg).subAggregation(departNameAgg)
                        .subAggregation(PipelineAggregatorBuilders.bucketSelector("filter_negative_profit",
                                profitMap,
                                new Script(ScriptType.INLINE, "painless", "params.profitSum > 0", Collections.emptyMap())))
                )
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms departments = aggregations.get("departments");

        List<KeyValueVO> list = Lists.newArrayList();
        for (Terms.Bucket entry : departments.getBuckets()) {
            String departmentCode = entry.getKeyAsString();
            ParsedSum sum = entry.getAggregations().get("total_profit");
            BigDecimal totalProfit = BigDecimalUtil.parseBigDecimal(sum.getValueAsString());
            totalProfit = totalProfit.abs();

            TopHits topHits = entry.getAggregations().get("doctorName");
            SearchHit[] hits = topHits.getHits().getHits();

            String departmentName = null;
            if (!ArrayUtil.isEmpty(hits)) {
                Map<String, Object> sourceAsMap = hits[0].getSourceAsMap();
                departmentName = (String) sourceAsMap.get("doctorName");
            }
            list.add(new KeyValueVO(departmentCode,totalProfit, departmentName) );
        }
        log.info("【EsDoctorGroupAngleDAO.getDoctorProfitTop5】 list: {}",JSONObject.toJSONString(list));

        return list;
    }

    /**
     *  医生亏损排名TOP5(DSL-107)
     * @param startDate
     * @param endDate
     * @return
     */
    public List<KeyValueVO> getDoctorLossTop5(LocalDate startDate, LocalDate endDate){
        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));
        if(startDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(startDate)));
        }
        if(endDate != null){
            queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lt(LocalDateUtil.getTimestamp(endDate.plusDays(1))));
        }

        // 构建聚合
        TermsAggregationBuilder departmentsAgg = AggregationBuilders.terms("departments")
                .field("doctorCode")
                .size(5)
                .order(BucketOrder.aggregation("total_profit", true));

        SumAggregationBuilder totalProfitAgg = AggregationBuilders.sum("total_profit").field("profit").format("0.00");

        TopHitsAggregationBuilder departNameAgg = AggregationBuilders.topHits("doctorName").size(1)
                .fetchSource(new String[]{"doctorName"}, null);

        Map profitMap = Maps.newHashMap();
        profitMap.put("profitSum", "total_profit");

        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .withPageable(PageRequest.of(0, 1))
                .addAggregation(departmentsAgg.subAggregation(totalProfitAgg).subAggregation(departNameAgg)
                        .subAggregation(PipelineAggregatorBuilders.bucketSelector("filter_negative_profit",
                                profitMap,
                                new Script(ScriptType.INLINE, "painless", "params.profitSum < 0", Collections.emptyMap())))
                )
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms departments = aggregations.get("departments");

        List<KeyValueVO> list = Lists.newArrayList();
        for (Terms.Bucket entry : departments.getBuckets()) {
            String departmentCode = entry.getKeyAsString();
            ParsedSum sum = entry.getAggregations().get("total_profit");
            BigDecimal totalProfit = BigDecimalUtil.parseBigDecimal(sum.getValueAsString());
            totalProfit = totalProfit.abs();

            TopHits topHits = entry.getAggregations().get("doctorName");
            SearchHit[] hits = topHits.getHits().getHits();

            String departmentName = null;
            if (!ArrayUtil.isEmpty(hits)) {
                Map<String, Object> sourceAsMap = hits[0].getSourceAsMap();
                departmentName = (String) sourceAsMap.get("doctorName");
            }
            list.add(new KeyValueVO(departmentCode,totalProfit, departmentName) );
        }
        log.info("【EsDoctorGroupAngleDAO.getDoctorLossTop5】 list: {}",JSONObject.toJSONString(list));

        return list;
    }



    /**
     * 医生盈亏总趋势(DSL-108)
     * @param year
     * @param doctorCodeList
     * @return
     */
    public ChartLineVO getDoctorCostTrend(Integer year, List<String> doctorCodeList){
        if(year == null){
            throw new ApiException("请选择年份");
        }

        List<String> dataX = BusinessDateUtil.getMonthList(year);

        // 检索条件
        BoolQueryBuilder queryBuilder = boolQuery();
        queryBuilder.must(QueryBuilders.termQuery("hasOut", true));


        LocalDateTime firstDayOfYear = LocalDateUtil.getFirstDayOfYear(year);
        LocalDateTime lastDayOfYear = LocalDateUtil.getLastDayOfYear(year);
        queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").gte(LocalDateUtil.getTimestamp(firstDayOfYear)));
        queryBuilder.must(QueryBuilders.rangeQuery("outHospitalDate").lte(LocalDateUtil.getTimestamp(lastDayOfYear)));


        if(!CollectionUtils.isEmpty(doctorCodeList)){
            queryBuilder.must(QueryBuilders.termsQuery("doctorCode",doctorCodeList));
        }


        DateHistogramAggregationBuilder dateHistogramAgg = AggregationBuilders.dateHistogram("monthly_profit")
                .field("outHospitalDate")
                .calendarInterval(DateHistogramInterval.MONTH)
                .format("yyyy-MM")
                .timeZone(ZoneId.of("+08:00"))
                .subAggregation(AggregationBuilders.sum("total_profit").field("profit").format("0.00"));

        TermsAggregationBuilder doctorsTerms = AggregationBuilders.terms("doctorAgg")
                .field("doctorCode")
                .size(5)
                .subAggregation(AggregationBuilders.topHits("doctorName").size(1)
                        .fetchSource(new String[]{"doctorName"}, null))
                .subAggregation(dateHistogramAgg);


        NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                .withQuery(queryBuilder)
                .addAggregation(doctorsTerms)
                .withPageable(PageRequest.of(0, 1))
                .build();

        Aggregations aggregations =  elasticsearchOperations.search(searchQuery, PatientCaseEsPO.class).getAggregations();
        Terms doctorTerms = aggregations.get("doctorAgg");

        List<ChartLineSeriesVO> series = new ArrayList<>();

        for (Terms.Bucket entry : doctorTerms.getBuckets()) {
            String doctorCode = entry.getKeyAsString();

            TopHits topHits = entry.getAggregations().get("doctorName");
            SearchHit[] hits = topHits.getHits().getHits();
            String groupName = null;
            if (!ArrayUtil.isEmpty(hits)) {
                Map<String, Object> sourceAsMap = hits[0].getSourceAsMap();
                groupName = (String) sourceAsMap.get("doctorName");
            }

            Histogram perMonthAgg = entry.getAggregations().get("monthly_profit");
            Map<String, Object> dataMap = getMonthlyMap(perMonthAgg);
            List<Object> dataYList = Lists.newArrayList();
            for(String month: dataX){
               Object value = dataMap.get(month);
                dataYList.add(ObjectUtils.isEmpty(value) ? null : value);
            }

            ChartLineSeriesVO seriesVO = new ChartLineSeriesVO();
            seriesVO.setSeriesName(groupName);
            seriesVO.setCode(doctorCode);
            seriesVO.setDataList(dataYList);
            series.add(seriesVO);
        }

        ChartLineVO vo = new ChartLineVO();
        vo.setDataX(dataX);
        vo.setSeries(series);

        log.info("【EsDoctorGroupAngleDAO.getCostTrend】 vo: {}",JSONObject.toJSONString(vo));
        return vo;
    }

    private Map<String, Object> getMonthlyMap(Histogram perMonthAgg){
        Map<String, Object> map = Maps.newHashMap();
        List<? extends Histogram.Bucket> buckets = perMonthAgg.getBuckets();
        for (Histogram.Bucket bucket : buckets) {
            String month = bucket.getKeyAsString();
            ParsedSum sum = bucket.getAggregations().get("total_profit");
            BigDecimal totalProfit = BigDecimalUtil.parseBigDecimal(sum.getValueAsString());
            map.put(month, totalProfit);
        }
        return map;
    }
}
